package ch.uzh.ifi.seal.ssqa.importer.issuetracking;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import org.eclipse.core.runtime.FileLocator;
import org.eclipse.core.runtime.IStatus;
import org.eclipse.core.runtime.Platform;
import org.eclipse.core.runtime.Status;
import org.eclipse.jface.dialogs.ErrorDialog;
import org.eclipse.ui.PlatformUI;
import org.hibernate.Session;

import ch.uzh.ifi.seal.ssqa.model.Action;
import ch.uzh.ifi.seal.ssqa.model.Issue;
import ch.uzh.ifi.seal.ssqa.model.ModelFactory;
import ch.uzh.ifi.seal.ssqa.model.Person;
import ch.uzh.ifi.seal.ssqa.model.TimeTracking;
import ch.uzh.ifi.seal.ssqa.storage.IStorage;
import ch.uzh.ifi.seal.ssqa.storage.StorageResult;
import ch.uzh.ifi.seal.ssqa.storage.TeneoHibernateStorage;

public class ClearQuestImporter {

	public static final String SEG = "#property#"; //if this segmentation gets changed, the according String has to be adapted in the Perl Script
	
	private HashMap<String,Issue> issues;
	private HashMap<String, Person> users;
	private int count = 0;
	private IStorage storage;
	private List<TimeTracking> timetrackings = new ArrayList<TimeTracking>();
	
	/**
	 * stores the data model to the database at the end. 
	 */
	public void persistData(){
		this.readoutUsers();
		this.getAllIssues();
		this.storage = new TeneoHibernateStorage();
		storage.saveOrUpdateEntity(issues.values());
		storage.saveOrUpdateEntity(timetrackings);
		this.persistDependentData();
	}
	
	/**
	 * All data, that uses two model entities has to be added at the end not to become a problem with the duplicated nodes.
	 */
	private void persistDependentData(){
		this.mapChildrens();
		this.mapTimeTracking();
		this.mapActions();
	}
	
	
	private HashMap<String, Issue> getAllIssues() {
		this.issues = new HashMap<String, Issue>();
		String sql = "select " +
				"T1.id," +						//0
				"T1.headline," +				//1
				"T1.software_version," +		//2
				"T1.reproducable," +			//3
				"T1.bug_type," +				//4
				"T7.platform," +				//5
				"T1.location," +				//6
				"T8.assembly_group," +			//7
				"T9.product," +					//8
				"T10.assembly_name," +			//9
				"T1.priority," +				//10
				"T1.severtity," +				//11
				"T11.originator_name," +		//12
				"T3.login_name," +				//13
				"T4.login_name," +				//14
				"T1.detailed_description_1, " +	//15
				"T1.submit_date, " +			//16				
				"T1.trk_start_date_impl, " +	//17
				"T1.trk_end_date_impl, " +		//18
				"T1.deadline_resolved, " +		//19
				"T1.trk_actual_work_impl, " +	//20
				"T1.trk_actual_work_review, " +	//21
				"T1.trk_actual_work_test, " +	//22
				"T1.trk_actual_work_total, " +	//23
				"T1.trk_planned_work_impl," +	//24
				"T1.trk_planned_work_review, " +	//25
				"T1.trk_planned_work_test, " +	//26
				"T1.trk_planned_work_total " +	//27
				"from cr T1,platform T7,assembly_group T8,product T9,assembly_record T10," +
				"originator T11,users T3,users T4 " +
				"where" +
				" T1.platform = T7.dbid and " +
				"T1.assembly_group = T8.dbid and T1.product = T9.dbid and " +
				"T1.assembly = T10.dbid and T1.originator = T11.dbid and T1.submitter = T3.dbid " +
				"and T1.owner = T4.dbid and (T1.dbid <> 0 and ((T7.platform = 'PF91'))) ";
				//"and T1.id='PF9100199759'";
		
		BufferedReader input = this.executePerlSQLScript(sql);
		
		String line = null;
		try {
			while ((line = input.readLine()) != null && line.trim() != "") {
				if(line.startsWith("PF")){
					Issue issue = ModelFactory.eINSTANCE.createIssue();
					TimeTracking tt = ModelFactory.eINSTANCE.createTimeTracking();
					this.parseIssue(line, issue, tt);
					if(issue != null && issue.getId() != ""){
						issues.put(issue.getId(), issue);
						timetrackings.add(tt);
					}
				}else{
					System.out.println("[CQ]: " + line);
				}				
			}
		} catch (IOException e) {
			System.out.println("Error at readLine()");
		}
		return issues;
		
	}
	
	/**
	 * parses each line returned by the Perl script.
	 * @param line
	 * @return
	 */
	private Issue parseIssue(String line){
		String[] issueArray = line.split(ClearQuestImporter.SEG);
		count++;
		
		try{
			Issue issue = ModelFactory.eINSTANCE.createIssue();
			if(issueArray[0] != null && issueArray[0].trim() != "") {
				issue.setId(issueArray[0]);
			}
			
			if(issueArray[1] != null && issueArray[1].trim() != "") {
				issue.setHeadline(issueArray[1]);
			}
			
			if(issueArray[2] != null && issueArray[2].trim() != "") {
				issue.setFoundInVersion(issueArray[2]);
			}
			
			if(issueArray[3] != null && issueArray[3].trim() != "") {
				issue.setReproducible((issueArray[3] == "Yes") ? true : false);
			}
			
			if(issueArray[4] != null && issueArray[4].trim() != "") {
				issue.setType(issueArray[4]);
			}
			
			if(issueArray[5] != null && issueArray[5].trim() != "") {
				issue.setPlatform(issueArray[5]);
			}
			
			if(issueArray[6] != null && issueArray[6].trim() != "") {
				issue.setLocation(issueArray[6]);
			}
			
			if(issueArray[7] != null && issueArray[7].trim() != "") {
				issue.setDiscipline(issueArray[7]);
			}
			
			if(issueArray[8] != null && issueArray[8].trim() != "") {
				issue.setProduct(issueArray[8]);
			}
			
			if(issueArray[9] != null && issueArray[9].trim() != "") {
				issue.setAssembly(issueArray[9]);
			}
			
			if(issueArray[10] != null && issueArray[10].trim() != "") {
				issue.setPriority(issueArray[10]);
			}
			
			if(issueArray[11] != null && issueArray[11].trim() != "") {
				issue.setSeverity(issueArray[11]);
			}
			
			if(issueArray[12] != null && issueArray[12].trim() != "") {
				Person originator = ModelFactory.eINSTANCE.createPerson();
				originator.setName(issueArray[12]);
				issue.setOriginator(originator);
			}
			
			if(issueArray[13] != null && issueArray[13].trim() != ""){
				issue.setSubmitter(this.users.get(issueArray[13]));
			}
			
			if(issueArray[14] != null && issueArray[14].trim() != ""){
				issue.setOwner(this.users.get(issueArray[14]));
			}
			
			if(issueArray[15] != null && issueArray[15].trim() != ""){
				issue.setDescription(issueArray[15]);
			}
			
			return issue;
		} catch (Exception e){
			System.out.println("Error: "+ e.getMessage());
			System.out.println(Arrays.toString(issueArray));
			return null;
		}
	}

	private void readoutUsers(){
			this.users = new HashMap<String, Person>();
			String sql = "select T1.login_name, T1.fullname from users T1";
			BufferedReader input = this.executePerlSQLScript(sql);
			String line = null;
			try {
				while ((line = input.readLine()) != null) {
					String[] personArray = line.split(ClearQuestImporter.SEG);
					if(personArray.length>1){
						Person person = ModelFactory.eINSTANCE.createPerson();
						String identifier = personArray[0];
						person.setIdentifier(identifier);
						person.setName(personArray[1]);
						this.users.put(identifier, person);
					}
				}
			} catch (IOException e) {
				System.out.println("Error at readLine()");
			}
	}

	private Date parseDate(String dateString){
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			return format.parse(dateString);
		} catch (ParseException e) {
			return null;
		}
	}
	
	private void mapChildrens(){
		String sql = "select distinct T1.id,T59.id from ( ( ( cr T1 INNER JOIN platform T7 ON T1.platform = T7.dbid ) LEFT OUTER JOIN parent_child_links T59mm ON T1.dbid = T59mm.parent_dbid  and 16779214 = T59mm.parent_fielddef_id  ) LEFT OUTER JOIN cr T59 ON T59mm.child_dbid = T59.dbid  ) where T1.dbid <> 0 and ((T7.platform = 'PF91'))";
		BufferedReader input = this.executePerlSQLScript(sql);
		String line = null;
		try {
			while ((line = input.readLine()) != null && line.trim() != "") {
				String[] issue = line.split(ClearQuestImporter.SEG);
				if(issue.length == 2 && issue[1] != null && issue[1].trim() != "" && issue[0] != null && issue[0].trim() != ""){
					StorageResult result = this.storage.executeUniqueQuery("from Issue where id = '"+issue[0]+"'");
					Session session = result.getSession();
					
					Issue objectIssue = (Issue)session.createQuery("from Issue where id = '"+issue[1]+"'").uniqueResult();
					Issue subjectIssue = (Issue)result.getResult();
					
					if(subjectIssue != null && objectIssue != null){
						subjectIssue.getChildren().add(objectIssue);
						System.out.println("ID with Children: " + subjectIssue.getId());
						session.getTransaction().commit();
						session.close();
					}
				}
			}
		} catch (IOException e) {
			System.out.println("MKN : Error at readLine()");
		}
	}
	
	private void mapTimeTracking(){
		String sql = "select distinct " +
				"T1.id," +
				"T1.submit_date," +
				"T1.trk_start_date_impl," +
				"T1.trk_end_date_impl," +
				"T1.deadline_resolved," +
				"T1.trk_actual_work_impl," +
				"T1.trk_actual_work_review," +
				"T1.trk_actual_work_test," +
				"T1.trk_actual_work_total," +
				"T1.trk_planned_work_impl," +
				"T1.trk_planned_work_review," +
				"T1.trk_planned_work_test," +
				"T1.trk_planned_work_total " +
				"from cr T1,platform T5 where T1.platform = T5.dbid and (T1.dbid <> 0 and ((T5.platform = 'PF91')))";
		BufferedReader input = this.executePerlSQLScript(sql);
		String line = null;
		try {
			while ((line = input.readLine()) != null && line.trim() != "") {
				String[] trackingArray = line.split(ClearQuestImporter.SEG);
				if(trackingArray[0] != null && trackingArray[0].trim() != ""){
					TimeTracking tracking = ModelFactory.eINSTANCE.createTimeTracking();
					Date date;
					if( (date= this.parseDate(trackingArray[1])) != null){
						tracking.setSubmissionDate(date);
					}
					if( (date= this.parseDate(trackingArray[2])) != null){
						tracking.setStartDate(date);
					}
					if( (date= this.parseDate(trackingArray[3])) != null){
						tracking.setEndDate(date);
					}
					if( (date= this.parseDate(trackingArray[4])) != null){
						tracking.setDeadline(date);
					}
					tracking.setActualTime(Double.parseDouble(trackingArray[5])+Double.parseDouble(trackingArray[6])+Double.parseDouble(trackingArray[7]));
					tracking.setEstimationTime(Double.parseDouble(trackingArray[9])+Double.parseDouble(trackingArray[10])+Double.parseDouble(trackingArray[11]));
					
					StorageResult result = this.storage.executeUniqueQuery("from Issue where id = '"+trackingArray[0]+"'");
					Issue subjectIssue = (Issue)result.getResult();
					if(subjectIssue != null){
						subjectIssue.setTimeTracking(tracking);
						result.getSession().getTransaction().commit();
					}
					
					result.getSession().close();
				}
			}
		} catch (IOException e){
			System.out.println("MKN : Error at readLine()");
		}
	}
	
	private void mapActions(){
		String sql = "select distinct " +
				"T1.id," +
				"T3.action_name," +
				"T3.action_timestamp," +
				"T3.user_name," +
				"T3.new_state," +
				"T3.old_state " +
				"from cr T1,history T3,platform T9 where T1.dbid = T3.entity_dbid  and 16777433 = " +
				"T3.entitydef_id  and T1.platform = T9.dbid and (T1.dbid <> 0 and ((T9.platform = 'PF91')))";
		BufferedReader input = this.executePerlSQLScript(sql);
		String line = null;
		try {
			while ((line = input.readLine()) != null && line.trim() != "") {
				String[] actionArray = line.split(ClearQuestImporter.SEG);
				if(actionArray[0] != null && actionArray[0].trim() != ""){
					Action action = ModelFactory.eINSTANCE.createAction();
					action.setAction(actionArray[1]);
					Date date; 
					if( (date= this.parseDate(actionArray[2])) != null){
						action.setTimestamp(date);
					}
					
					if(actionArray[3] != null && actionArray[3].trim() != ""){
							action.setOriginatorId(actionArray[3]);
					}
					
					action.setNewState(actionArray[4]);
					action.setOldState(actionArray[5]);
					
					StorageResult result = this.storage.executeUniqueQuery("from Issue where id = '"+actionArray[0]+"'");
					Issue subjectIssue = (Issue)result.getResult();
					if(subjectIssue != null){
						subjectIssue.getActions().add(action);
						result.getSession().getTransaction().commit();
					}
					result.getSession().close();
				}
			}
		} catch (IOException e){
			System.out.println("MKN : Error at readLine()");
		}
	}
	
	
	private void parseIssue(String line, Issue issue, TimeTracking tt) {
		String[] issueArray = line.split(ClearQuestImporter.SEG);
		count++;
		
		try{			
			if(issueArray[0] != null && issueArray[0].trim() != "") {
				issue.setId(issueArray[0]);
				System.out.println("Parse CQ: " + issueArray[0]);
			}
			
			if(issueArray[1] != null && issueArray[1].trim() != "") {
				issue.setHeadline(issueArray[1]);
			}
			
			if(issueArray[2] != null && issueArray[2].trim() != "") {
				issue.setFoundInVersion(issueArray[2]);
			}
			
			if(issueArray[3] != null && issueArray[3].trim() != "") {
				issue.setReproducible((issueArray[3] == "Yes") ? true : false);
			}
			
			if(issueArray[4] != null && issueArray[4].trim() != "") {
				issue.setType(issueArray[4]);
			}
			
			if(issueArray[5] != null && issueArray[5].trim() != "") {
				issue.setPlatform(issueArray[5]);
			}
			
			if(issueArray[6] != null && issueArray[6].trim() != "") {
				issue.setLocation(issueArray[6]);
			}
			
			if(issueArray[7] != null && issueArray[7].trim() != "") {
				issue.setDiscipline(issueArray[7]);
			}
			
			if(issueArray[8] != null && issueArray[8].trim() != "") {
				issue.setProduct(issueArray[8]);
			}
			
			if(issueArray[9] != null && issueArray[9].trim() != "") {
				issue.setAssembly(issueArray[9]);
			}
			
			if(issueArray[10] != null && issueArray[10].trim() != "") {
				issue.setPriority(issueArray[10]);
			}
			
			if(issueArray[11] != null && issueArray[11].trim() != "") {
				issue.setSeverity(issueArray[11]);
			}
			
			if(issueArray[12] != null && issueArray[12].trim() != "") {
				Person originator = ModelFactory.eINSTANCE.createPerson();
				originator.setName(issueArray[12]);
				issue.setOriginator(originator);
			}
			
			if(issueArray[13] != null && issueArray[13].trim() != ""){
				issue.setSubmitter(this.users.get(issueArray[13]));
			}
			
			if(issueArray[14] != null && issueArray[14].trim() != ""){
				issue.setOwner(this.users.get(issueArray[14]));
			}
			
			if(issueArray[15] != null && issueArray[15].trim() != ""){
				issue.setDescription(issueArray[15]);
			}
			
/*			"T1.trk_start_date_impl," +		//17
			"T1.trk_end_date_impl," +		//18
			"T1.deadline_resolved," +		//19
			"T1.trk_actual_work_impl," +	//20
			"T1.trk_actual_work_review," +	//21
			"T1.trk_actual_work_test," +	//22
			"T1.trk_actual_work_total," +	//23
			"T1.trk_planned_work_impl," +	//24
			"T1.trk_planned_work_review," +	//25
			"T1.trk_planned_work_test," +	//26
			"T1.trk_planned_work_total " +	//27
*/	
			Date date;
			if((date= this.parseDate(issueArray[16])) != null){
				tt.setSubmissionDate(date);
			}
			if((date= this.parseDate(issueArray[17])) != null){
				tt.setStartDate(date);
			}
			if((date= this.parseDate(issueArray[18])) != null){
				tt.setEndDate(date);
			}
			if((date= this.parseDate(issueArray[19])) != null){
				tt.setDeadline(date);
			}
			if((date= this.parseDate(issueArray[20])) != null){
				//tt.setActualTime(value)
			}
			if((date= this.parseDate(issueArray[21])) != null){
				tt.setSubmissionDate(date);
			}
			if((date= this.parseDate(issueArray[22])) != null){
				tt.setSubmissionDate(date);
			}
			tt.setActualTime(Double.parseDouble(issueArray[23]));
			tt.setEstimationTime(Double.parseDouble(issueArray[27]));
			issue.setTimeTracking(tt);
		} catch (Exception e){
			e.printStackTrace();
			System.out.println("Parse Issue and timetracking has Error: "+ e.getMessage());
			System.out.println(line);
		}
	}

	
	
	private BufferedReader executePerlSQLScript(String sql){
			String path;
			try {
				path = FileLocator.toFileURL(Platform.getBundle("ch.uzh.ifi.seal.ssqa").getEntry("/scripts/")).getPath();
				path = (path.startsWith("/")) ? path.replaceFirst("/", "") : path;
				String command = "ratlperl "+path+"cq_sql.pl \""+sql+"\"";
				Runtime rt = Runtime.getRuntime();
				Process pr = rt.exec(command);
				BufferedReader input = new BufferedReader(new InputStreamReader(pr.getInputStream()));
				return input;
			} catch (IOException e) {
				Status status = new Status(IStatus.ERROR, "My Plug-in ID", 0, "There is no ClearQuest instance around.", null);
				ErrorDialog.openError(PlatformUI.getWorkbench().getDisplay().getActiveShell(), "ClearQuest Error", "There is no ClearQuest instance around.", status);
				System.out.println("No ClearQuest accessible");
				return null;
			}
	}
}
